import sys
from PyQt6.QtWidgets import (
    QApplication, QMainWindow, QTableView, QPushButton, 
    QVBoxLayout, QHBoxLayout, QWidget, QMessageBox, QLineEdit,
)
from PyQt6.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery
from PyQt6.QtCore import Qt

class StudentManagementSystem(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("学生管理系统")
        self.resize(800, 600)
        
        # 初始化UI
        self.init_ui()
        
        # 初始化数据库
        self.init_db()
        
    def init_ui(self):
        """初始化用户界面"""
        # 中央部件
        central_widget = QWidget()
        self.setCentralWidget(central_widget)
        
        # 主布局
        main_layout = QVBoxLayout()
        central_widget.setLayout(main_layout)
        
        # 创建表格视图
        self.tableView = QTableView()
        main_layout.addWidget(self.tableView)
        
        # 按钮布局
        button_layout = QHBoxLayout()
        main_layout.addLayout(button_layout)
        
        # 添加按钮
        self.addButton = QPushButton("添加")
        self.addButton.clicked.connect(self.add_record)
        button_layout.addWidget(self.addButton)
        
        # 删除按钮
        self.deleteButton = QPushButton("删除")
        self.deleteButton.clicked.connect(self.delete_record)
        button_layout.addWidget(self.deleteButton)
        
        # 保存按钮
        self.saveButton = QPushButton("保存")
        self.saveButton.clicked.connect(self.save_changes)
        button_layout.addWidget(self.saveButton)
        
        # 取消按钮
        self.cancelButton = QPushButton("取消")
        self.cancelButton.clicked.connect(self.cancel_changes)
        button_layout.addWidget(self.cancelButton)
        
        # 添加查询布局 (新增代码)
        search_layout = QHBoxLayout()
        main_layout.addLayout(search_layout)
        
        # 查询输入框
        self.searchInput = QLineEdit()
        self.searchInput.setPlaceholderText("输入姓名或专业查询")
        search_layout.addWidget(self.searchInput)
        
        # 查询按钮
        self.searchButton = QPushButton("查询")
        self.searchButton.clicked.connect(self.search_records)
        search_layout.addWidget(self.searchButton)
        
        # 重置按钮
        self.resetButton = QPushButton("重置")
        self.resetButton.clicked.connect(self.reset_search)
        search_layout.addWidget(self.resetButton)

    def init_db(self):
        """初始化数据库连接和模型"""
        # 创建一个新的数据库连接
        self.db = QSqlDatabase.addDatabase('QODBC')
        
        # 配置连接参数
        connection_string = (
            "DRIVER={MySQL ODBC 9.3 Unicode Driver};"  # 确保这个驱动名称正确
            "SERVER=localhost;"
            "DATABASE=student_management;"
            "USER=root;"
            "PASSWORD=936580;"  # 替换为你的MySQL密码
            "CHARSET=utf8mb4;"
        )
        
        self.db.setDatabaseName(connection_string)
        
        # 尝试打开数据库
        if self.db.open():
            # 检查表是否存在，不存在则创建
            query = QSqlQuery()
            if not query.exec("SHOW TABLES LIKE 'student'"):
                self.statusLabel.setText(f"检查表失败: {query.lastError().text()}")
                return False
                
            if not query.next():  # 表不存在，创建表
                self.createTables()

            # 创建模型并设置表格
            self.model = QSqlTableModel(self, self.db)
            self.model.setTable("student")  # 确保表名与数据库完全一致
            self.model.setEditStrategy(QSqlTableModel.EditStrategy.OnManualSubmit)
            self.model.setSort(0, Qt.SortOrder.AscendingOrder)
            
            # 先执行select()测试模型是否正确
            if not self.model.select():
                error = self.model.lastError()
                print(f"数据库错误: {error.databaseText()}\n驱动错误: {error.driverText()}")
                # QMessageBox.critical(
                #     self, 
                #     "查询错误", 
                #     f"数据库错误: {error.databaseText()}\n驱动错误: {error.driverText()}\n完整错误: {error.text()}"
                # )
                return False
            
            # 设置表头（按实际字段顺序）
            self.model.setHeaderData(0, Qt.Orientation.Horizontal, "ID")
            self.model.setHeaderData(1, Qt.Orientation.Horizontal, "姓名")  
            self.model.setHeaderData(2, Qt.Orientation.Horizontal, "性别")
            self.model.setHeaderData(3, Qt.Orientation.Horizontal, "年龄")
            self.model.setHeaderData(4, Qt.Orientation.Horizontal, "专业")
        
            # 将模型设置到表格视图
            self.tableView.setModel(self.model)
            self.tableView.resizeColumnsToContents()
            
            return True
        else:
            QMessageBox.critical(
                self, 
                "数据库错误", 
                f"无法连接数据库: {self.db.lastError().text()}"
            )
            return False
        
    def createTables(self):
        """创建数据库表"""
        queries = [
            "CREATE TABLE student ("
            "  ID INT AUTO_INCREMENT PRIMARY KEY,"
            "  stu_name VARCHAR(100) NOT NULL,"
            "  gender ENUM('男', '女') NOT NULL,"
            "  age INT,"
            "  major VARCHAR(100)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
            
            "INSERT INTO student (stu_name, gender, age, major) VALUES "
            "('张三', '男', 20, '计算机科学'),"
            "('李四', '女', 22, '大数据'),"
            "('王五', '男', 21, '人工智能');"
        ]
        for sql in queries:
            query = QSqlQuery()
            if not query.exec(sql):
                print(f"创建表失败: {query.lastError().text()}")
                return False
        return True  

    def add_record(self):
        """在表格末尾添加一条新记录"""
        # 获取当前行数
        row_count = self.model.rowCount()
        
        # 插入新行（关键修改）
        if not self.model.insertRow(row_count):
            QMessageBox.critical(self, "错误", "无法添加新行")
            return
        
        # 选中新行
        self.tableView.selectRow(row_count)
        
    def delete_record(self):
        """删除选中的记录"""
        # 获取当前选中行
        selected_rows = self.tableView.selectionModel().selectedRows()
        
        if not selected_rows:
            QMessageBox.warning(self, "警告", "请先选择要删除的行")
            return
        
        # 确认删除
        reply = QMessageBox.question(
            self, 
            "确认删除", 
            "确定要删除选中的记录吗?", 
            QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No
        )
        
        if reply == QMessageBox.StandardButton.No:
            return
        
        # 删除选中的行
        for row in reversed(sorted(selected_rows)):
            self.model.removeRow(row.row())
    
    def save_changes(self):
        """保存所有修改到数据库"""
        # 开始事务
        self.db.transaction()
        
        # 尝试提交所有修改
        if not self.model.submitAll():
            # 如果提交失败，回滚事务并显示错误
            self.db.rollback()
            QMessageBox.critical(
                self, 
                "保存错误", 
                f"无法保存修改: {self.model.lastError().text()}"
            )
        else:
            # 提交成功，提交事务
            self.db.commit()
            QMessageBox.information(self, "成功", "修改已保存")
            
            # 刷新数据
            self.model.select()
    
    def cancel_changes(self):
        """取消所有未保存的修改"""
        # 回滚所有未提交的修改
        self.model.revertAll()
        QMessageBox.information(self, "提示", "修改已取消")
        
        # 刷新数据
        self.model.select()

    def search_records(self):
        """执行查询操作"""
        keyword = self.searchInput.text().strip()
        if not keyword:
            QMessageBox.warning(self, "提示", "请输入查询关键词")
            return
        
        try:
            # 修正后的过滤条件，使用实际字段名
            filter_str = f"stu_name LIKE '%{keyword}%' OR major LIKE '%{keyword}%'"
            self.model.setFilter(filter_str)
            
            if not self.model.select():
                error = self.model.lastError()
                QMessageBox.critical(
                    self, 
                    "查询错误", 
                    f"查询失败: {error.text()}\n"
                    f"实际执行的SQL: {self.model.query().lastQuery()}"
                )
            else:
                # 自动调整列宽
                self.tableView.resizeColumnsToContents()
        except Exception as e:
            QMessageBox.critical(self, "错误", f"查询时发生异常: {str(e)}")

    def reset_search(self):
        """重置查询结果"""
        self.searchInput.clear()
        self.model.setFilter("")  # 清除过滤条件
        self.model.select()  # 重新加载所有数据

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = StudentManagementSystem()
    window.show()
    sys.exit(app.exec())
